Imports System.Data.OracleClient


Public Class clsVrbl

  Public mCmdVrbl As OracleCommand
  Public mDrVrbl As OracleDataReader

  Public mIntVrblIdxx As Integer
  Public mIntVrblSqnce As Integer
  Public mStrVrblName As String
  Public mIntVrblMclsIdxx As Integer
  Public mIntVrblSclsIdxx As Integer
  Public mStrVrblDataType As String
  Public mStrVrblScope As String
  Public mStrVrblType As String
  Public mStrVrblSize As String
  Public mStrVrblRefrName As String
  Public mStrVrblFixxName As String
  Public mStrVrblNullIndc As String





  Public Function fnSelectVrbl() As Boolean

    Dim lStrQuery As String = ""



    If mIntVrblIdxx <> 0 Then
      lStrQuery = "select VRBL_IDXX,VRBL_SQNC,VRBL_NAME,VRBL_MCLS_IDXX,VRBL_SCLS_IDXX,"
      lStrQuery = lStrQuery & "VRBL_DATA_TYPE,VRBL_SCOP,VRBL_TYPE,VRBL_SIZE,VRBL_REFR_NAME,"
      lStrQuery = lStrQuery & "VRBL_FIXX_NAME,VRBL_NULL_INDC from TRDX_VARIABLES where VRBL_IDXX = " & mIntVrblIdxx

      mCmdVrbl = New OracleCommand(lStrQuery, con)
      mDrVrbl = mCmdVrbl.ExecuteReader

      fnSelectVrbl = True
    Else
      MessageBox.Show("Invalid Values", gCONST_MSG_HEADER, MessageBoxButtons.OK)
      fnSelectVrbl = False
    End If

  End Function




  Public Function fnInsertVrbl() As Integer

    Dim lIntSeqNum As Integer
    Dim lStrQuery As String



    mCmdVrbl = New OracleCommand("select count(*) as CNT from TRDX_VARIABLES", con)
    Dim nCount As Integer = mCmdVrbl.ExecuteScalar()

    If nCount = 0 Then
      lIntSeqNum = 0
    Else
      mCmdVrbl = New OracleCommand("select max(VRBL_IDXX) as CNT from TRDX_VARIABLES", con)
      lIntSeqNum = mCmdVrbl.ExecuteScalar
    End If

    lStrQuery = "insert into TRDX_VARIABLES values(" & (lIntSeqNum + 1) & ","
    lStrQuery = lStrQuery & mIntVrblSqnce & ",'" & mStrVrblName & "',"
    lStrQuery = lStrQuery & mIntVrblMclsIdxx & "," & mIntVrblSclsIdxx & ",'"
    lStrQuery = lStrQuery & mStrVrblDataType & "','" & mStrVrblScope & "','"
    lStrQuery = lStrQuery & mStrVrblType & "','" & mStrVrblSize & "','"
    lStrQuery = lStrQuery & mStrVrblRefrName & "','" & mStrVrblFixxName & "','"

    If Trim(mStrVrblNullIndc) = "" Then
      mStrVrblNullIndc = "N"
    End If

    lStrQuery = lStrQuery & mStrVrblNullIndc & "')"
    mCmdVrbl = New OracleCommand(lStrQuery, con)
    mCmdVrbl.ExecuteNonQuery()

    fnInsertVrbl = lIntSeqNum + 1

  End Function





  Public Function fnUpdateVrbl() As Boolean

    Dim lStrQuery As String



    mCmdVrbl = New OracleCommand("select count(*) as CNT from TRDX_VARIABLES where VRBL_IDXX = " _
                                & mIntVrblIdxx, con)
    Dim ncount As Integer = mCmdVrbl.ExecuteScalar

    If ncount <> 0 Then
      lStrQuery = "update TRDX_VARIABLES set VRBL_SQNC = " & mIntVrblSqnce & ","
      lStrQuery = lStrQuery & "VRBL_NAME = '" & mStrVrblName & "',"
      lStrQuery = lStrQuery & "VRBL_MCLS_IDXX = " & mIntVrblMclsIdxx & ","
      lStrQuery = lStrQuery & "VRBL_SCLS_IDXX = " & mIntVrblSclsIdxx & ","
      lStrQuery = lStrQuery & "VRBL_DATA_TYPE = '" & mStrVrblDataType & "',"
      lStrQuery = lStrQuery & "VRBL_SCOP = '" & mStrVrblScope & "',"
      lStrQuery = lStrQuery & "VRBL_TYPE = '" & mStrVrblType & "',"
      lStrQuery = lStrQuery & "VRBL_SIZE = '" & mStrVrblSize & "',"
      lStrQuery = lStrQuery & "VRBL_FIXX_NAME = '" & mStrVrblFixxName & "',"
      lStrQuery = lStrQuery & "VRBL_REFR_NAME = " & mStrVrblRefrName & ","

      If Trim(mStrVrblNullIndc) = "" Then mStrVrblNullIndc = "N"

      lStrQuery = lStrQuery & "VRBL_NULL_INDC = '" & mStrVrblNullIndc & "'"
      lStrQuery = lStrQuery & " where VRBL_IDXX = " & mIntVrblIdxx

      mCmdVrbl = New OracleCommand(lStrQuery, con)
      mCmdVrbl.ExecuteNonQuery()

      fnUpdateVrbl = True
    Else
      MessageBox.Show("Invalid values", gCONST_MSG_HEADER, MessageBoxButtons.OK)
      fnUpdateVrbl = False
    End If

  End Function





  Public Function fnSelectAllVrbl() As Boolean

    Dim lStrQuery As String = ""



    lStrQuery = "select VRBL_IDXX,VRBL_SQNC,VRBL_NAME,VRBL_MCLS_IDXX,VRBL_SCLS_IDXX,"
    lStrQuery = lStrQuery & "VRBL_DATA_TYPE,VRBL_SCOP,VRBL_TYPE,VRBL_SIZE,VRBL_REFR_NAME,"
    lStrQuery = lStrQuery & "VRBL_FIXX_NAME,VRBL_NULL_INDC from TRDX_VARIABLES"

    mCmdVrbl = New OracleCommand(lStrQuery, con)
    mDrVrbl = mCmdVrbl.ExecuteReader

    fnSelectAllVrbl = True

  End Function

  Public Function fnSelectVrblBasdMclsScls() As Boolean

    Dim lStrQuery As String = ""



    lStrQuery = "select VRBL_IDXX,VRBL_SQNC,VRBL_NAME,VRBL_MCLS_IDXX,VRBL_SCLS_IDXX,"
    lStrQuery = lStrQuery & "VRBL_DATA_TYPE,VRBL_SCOP,VRBL_TYPE,VRBL_SIZE,VRBL_REFR_NAME,"
    lStrQuery = lStrQuery & "VRBL_FIXX_NAME,VRBL_NULL_INDC from TRDX_VARIABLES where "
    lStrQuery = lStrQuery & "VRBL_MCLS_IDXX = " & mIntVrblMclsIdxx.ToString
    lStrQuery = lStrQuery & " AND VRBL_SCLS_IDXX = " & mIntVrblSclsIdxx.ToString
    lStrQuery = lStrQuery & "ORDER BY VRBL_SQNC"

    mCmdVrbl = New OracleCommand(lStrQuery, con)
    mDrVrbl = mCmdVrbl.ExecuteReader

    fnSelectVrblBasdMclsScls = True

  End Function
End Class
